package com.demo2do.fms.admin.entity.report.export;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collections;
import java.util.Map;
import java.util.WeakHashMap;

import org.springframework.jdbc.core.RowMapper;

import com.demo2do.core.persistence.support.QueryBuilder;
import com.demo2do.core.persistence.support.QueryFilter;
import com.demo2do.core.persistence.support.QueryOrder;

/**
 *
 * @author Ark
 *
 */
public class TruckDistanceReportSearch implements QueryBuilder  {

    /** 运输公司 */
    private Long organizationId;
    
    /** 导出年月 */
    private String yearMonth;
    
    /** 起始行 */
    private Integer beginIndex;
    
    /** 截止行 */
    private Integer endIndex;
    
    private QueryFilter queryFilter;
    
    private QueryOrder queryOrder;

    /**
     * The default constructor
     */
    public TruckDistanceReportSearch() {
        queryOrder = new QueryOrder("t1.vehicle_name", "ASC");
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#getPreparedFilter(boolean)
     */
    public QueryFilter getPreparedFilter(boolean refresh) {
        
        if (queryFilter == null || refresh) {
            
            queryFilter = new QueryFilter().addEquals("b", "trans_corp_id", organizationId);
            
            queryFilter.addSentence("TO_CHAR(b.leaving_time, 'YYYYMM') = " + yearMonth, Collections.emptyMap());
            
        }
        
        return queryFilter;
        
    }
    
    /**
     * @return
     */
    public String buildCountTemplate() {
        
        StringBuffer buffer = new StringBuffer(256);
        
        buffer.append("SELECT COUNT(*) ")
              .append("FROM (SELECT T1.VEHICLE_NAME AS VEHICLE_NAME, SUM (T2.FULL_LOAD_DISTANCE) AS FULL_LOAD_DISTANCE, SUM (T2.EMPTY_LOAD_DISTANCE) AS EMPTY_LOAD_DISTANCE ")
                    .append("FROM VW_VEHICLE T1, ")
                       .append("(SELECT DISTINCT B.DISPATCH_ORDER_ID, B.TRANS_MEANS_ID, A.FULL_LOAD_DISTANCE, A.EMPTY_LOAD_DISTANCE ")
                       .append("FROM TBL_TRUCK_TRANSPORT_COST A, TBL_ALL_SHIPMENT B ")
                       .append("WHERE A.DISPATCH_ORDER_ID = B.DISPATCH_ORDER_ID AND B.IS_DISABLED = 0 " + getPreparedFilter(false).clauseSentence() + ") T2 ")
              .append("WHERE T2.TRANS_MEANS_ID = T1.ID ")
              .append("GROUP BY T1.VEHICLE_NAME ) T_ALL  ");
        
        return buffer.toString();
        
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#buildSentenceTemplate()
     */
    public String buildSentenceTemplate() {
        
        StringBuffer buffer = new StringBuffer(256);
        
        buffer.append("SELECT VEHICLE_NAME AS vehicleName, ")
                     .append("FULL_LOAD_DISTANCE AS fullLoadDistance, ")
                     .append("EMPTY_LOAD_DISTANCE AS emptyLoadDistance ")
              .append("FROM (SELECT T1.VEHICLE_NAME AS VEHICLE_NAME, SUM (T2.FULL_LOAD_DISTANCE) AS FULL_LOAD_DISTANCE, SUM (T2.EMPTY_LOAD_DISTANCE) AS EMPTY_LOAD_DISTANCE ")
                      .append("FROM VW_VEHICLE T1, ")
                      .append("(SELECT DISTINCT B.DISPATCH_ORDER_ID, B.TRANS_MEANS_ID, A.FULL_LOAD_DISTANCE, A.EMPTY_LOAD_DISTANCE ")
                         .append("FROM TBL_TRUCK_TRANSPORT_COST A, TBL_ALL_SHIPMENT B ")
                         .append("WHERE A.DISPATCH_ORDER_ID = B.DISPATCH_ORDER_ID AND B.IS_DISABLED = 0 " + getPreparedFilter(false).clauseSentence() + ") T2 ")
               .append("WHERE T2.TRANS_MEANS_ID = T1.ID ")
               .append("GROUP BY T1.VEHICLE_NAME ")
               .append(queryOrder.sentence() + ") T_ALL");
        
        String sentence = "SELECT page.*, ROWNUM rn FROM (" + buffer.toString() + ") page WHERE ROWNUM <= " + endIndex;
        sentence = "SELECT * FROM (" + sentence + ") WHERE rn >= " + beginIndex;
      
        return sentence;
        
    }
    
    /**
     * build rowMapper
     * 
     * @return
     */
    public RowMapper<Map<String, Object>> buildRowMapper() {
        
        RowMapper<Map<String, Object>> rowMapper = new RowMapper<Map<String,Object>>() {
            
            /* (non-Javadoc)
             * @see org.springframework.jdbc.core.RowMapper#mapRow(java.sql.ResultSet, int)
             */
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                
                Map<String, Object> result = new WeakHashMap<String, Object>();
                
                result.put("vehicleName", rs.getObject("vehicleName"));
                result.put("fullLoadDistance", rs.getObject("fullLoadDistance"));
                result.put("emptyLoadDistance", rs.getObject("emptyLoadDistance"));
                
                return result;
                
            }
            
        };
        
        return rowMapper;
        
    }

    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#buildParameters()
     */
    public Map<String, Object> buildParameters() {
        return getPreparedFilter(false).parameters();
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#getOrder()
     */
    public QueryOrder getOrder() {
        return queryOrder;
    }

    /**
     * @return the organizationId
     */
    public Long getOrganizationId() {
        return organizationId;
    }

    /**
     * @return
     */
    public String getYearMonth() {
        return yearMonth;
    }

    /**
     * @return the beginIndex
     */
    public Integer getBeginIndex() {
        return beginIndex;
    }

    /**
     * @return the endIndex
     */
    public Integer getEndIndex() {
        return endIndex;
    }

    /**
     * @param organizationId the organizationId to set
     */
    public void setOrganizationId(Long organizationId) {
        this.organizationId = organizationId;
    }

    /**
     * @param yearMonth
     */
    public void setYearMonth(String yearMonth) {
        this.yearMonth = yearMonth;
    }

    /**
     * @param beginIndex the beginIndex to set
     */
    public void setBeginIndex(Integer beginIndex) {
        this.beginIndex = beginIndex;
    }

    /**
     * @param endIndex the endIndex to set
     */
    public void setEndIndex(Integer endIndex) {
        this.endIndex = endIndex;
    }
    
}
